\documentclass{article}
\usepackage{graphicx}
\usepackage{fancyvrb,relsize}
\setlength{\textwidth}{6.5in}
\setlength{\oddsidemargin}{-0.5in}
\setlength{\evensidemargin}{-0.5in}
\begin{document}

\title{Optimizing PED and TPED queries with respect to memory and runtime}
\author{Faheem Mitha}
\maketitle

\section{Background and Discussion}

This document does a comparison of different queries which generate
data files from a schema. For more details of the schema and
background to the project please see diag.pdf.

The data formats are discussed in detail at
http://pngu.mgh.harvard.edu/~purcell/plink/data.shtml

Since the data sets in question are quite large, performance is an
issue. I have listed a number of different queries which I have
tested, with appended EXPLAIN ANALYZE VERBOSE plans, and memory plots.

I'll focus on the queries for the PED data format, which are in
Section~\ref{ped}. Section~\ref{tped} is similar. The test data was
affy6 hapmap set, with over 800 million genotype calls, corresponding
to the geno table.

\begin{itemize}
\item The first version of this query with reasonable memory behavior
  is a big join in a subquery (Section~\ref{ped_bigjoin}). I had to
  use a subquery here to force an order for the array\_agg. This
  versions memory usage stays at around 5 GB most of the time, but has
  a runtime of 10 hrs.
\item My next attempt was Section~\ref{ped_trunc}. This is based on
  the observation that the big join in Section~\ref{ped_bigjoin}
  carries around a lot of data that it does nothing with, namely that
  dealing with patient information, which gets reproduced 800 million
  times (in this example) for no reason. So, I decided to remove the
  joins to tables corresponding to the patient data, namely pheno and
  sex, to see how it would affect performance, and the runtime dropped
  to 150 min, while the memory stayed around 5G.
\item I then decided to put back pheno and sex in
  Section~\ref{ped_phenoout}. So, I made pheno and sex join with the
  genotypic data after the aggregate has already taken place in a
  subquery. The runtime of this query stayed roughly constant, but the
  memory began to increase around 100 min and continued to increase
  till the end of the process at 150 min, with RSS peaking at around
  30 GB.
\item Next, I split the query into two queries
  (Section~\ref{ped_hybrid}), one of them the same as
  Section~\ref{ped_trunc}, and the rest just a query for pheno and
  ped, and glued them together using the Python SQLalchemy
  library. This one, unsurprisingly, has the same runtime and memory
  characteristics as Section~\ref{ped_trunc}. This is not as good as a
  join, in that patient info that doesn't have corresponding genotypic
  information will still be included in the resulting file.
\item Similar comments apply to Section~\ref{tped_bigjoin} and
  Section~\ref{tped_hybrid}. Section~\ref{tped_bigjoin} runtime is 300 min,
  which drops to 70 min for Section~\ref{tped_hybrid}.
\end{itemize}

In each case, runtime of the hybrid version is approximately 25\% of
the bigjoin version. If I have to I can use Section~\ref{ped_hybrid}
and Section~\ref{tped_hybrid}, but I am left wondering why I get the
performance I do out of the earlier versions. Specifically, why is
Section~\ref{ped_bigjoin} so much slower than Section~\ref{ped_trunc},
and why does the memory usage in Section~\ref{ped_phenoout} blow up
relative to Section~\ref{ped_bigjoin} and Section~\ref{ped_trunc}? The
EXPLAIN ANALYZE VERBOSE plans don't have answers for me, but perhaps I
am missing the obvious here. If I could get a single SQL queries with
the same memory and runtime performance as Section~\ref{ped_hybrid}
and Section~\ref{tped_hybrid}, I would use them instead.
\section{PED}\label{ped}

\subsection{PED bigjoin}\label{ped_bigjoin}
\subsubsection{PED bigjoin query}
\begin{Verbatim}
   SET search_path TO %s, public;
   WITH dedup_patient_anno AS
     ( SELECT *
     FROM
             (SELECT  *,
                      row_number() OVER(PARTITION BY anno.rsid ORDER BY anno.id)
             FROM     anno
                      INNER JOIN geno
                      ON       anno.id = geno.anno_id
             WHERE    idlink_id        =
                      (SELECT MIN(id)
                      FROM    idlink
                      )
             ) AS s
     WHERE   row_number = '1'
     )
     SELECT
                  gvals_ord.patientid
                  || E'\t'
                  || gvals_ord.patientid
                  || E'\t'
                  || 0
                  || E'\t'
                  || 0
                  || E'\t'
                  || COALESCE(gvals_ord.sex_id::text, '')
                  || E'\t'
                  || COALESCE(gvals_ord.phenotype::text,'')
                  || E'\t'
                  ||
                  array_to_string(array_agg(gvals_ord.g), E'\t') AS str
     FROM
     (
     SELECT decode_genotype(geno.snpval_id, allelea_id, alleleb_id) AS g,
     geno.idlink_id, geno.anno_id, pheno.patientid, pheno.phenotype, sex.code AS sex_id
     FROM    geno
     INNER JOIN dedup_patient_anno
     ON      geno.anno_id = dedup_patient_anno.id
     INNER JOIN idlink
     ON      geno.idlink_id = idlink.id
     INNER JOIN pheno
     ON      idlink.patientid = pheno.patientid
     INNER JOIN sex
     ON      pheno.sex_id=sex.val
     ORDER BY idlink_id, sex_id, phenotype, patientid, anno_id
             ) AS gvals_ord
     GROUP BY  gvals_ord. idlink_id, gvals_ord.sex_id, gvals_ord.phenotype, gvals_ord.patientid
     ORDER BY gvals_ord.idlink_id;
\end{Verbatim}

\subsubsection{PED bigjoin query plan  (output of EXPLAIN ANALYSE VERBOSE)}
\begin{Verbatim}[fontsize=\relsize{-1}]

GroupAggregate  (cost=59287066.94..62810437.01 rows=11552033 width=100) (actual time=23043163.670..37765996.956 rows=909 loops=1)
  Output: (((((((((((((pheno.patientid)::text || '	'::text) || (pheno.patientid)::text) || '	'::text) || '0'::text) || '	'::text) || '0'::text) || '	'::text) || COALESCE((sex.code)::text, ''::text)) || '	'::text) || COALESCE((pheno.phenotype)::text, ''::text)) || '	'::text) || array_to_string(array_agg((CASE WHEN (hapmap.geno.snpval_id = (-1)) THEN '0 0'::text WHEN (hapmap.geno.snpval_id = 0) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.allelea_id)::text) WHEN (hapmap.geno.snpval_id = 1) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) WHEN (hapmap.geno.snpval_id = 2) THEN (((dedup_patient_anno.alleleb_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) ELSE NULL::text END)), '	'::text))
  CTE dedup_patient_anno
    ->  Subquery Scan s  (cost=2401661.29..2431184.23 rows=4542 width=195) (actual time=374088.758..375757.128 rows=905486 loops=1)
          Output: s.id, s.fid, s.rsid, s.chromosome, s.location, s.allelea_id, s.alleleb_id, s.preferred, s.idlink_id, s.anno_id, s.snpval_id, s.row_number
          Filter: (s.row_number = 1::bigint)
          ->  WindowAgg  (cost=2401661.29..2419829.25 rows=908398 width=53) (actual time=374088.748..375463.283 rows=908398 loops=1)
                Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred, hapmap.geno.idlink_id, hapmap.geno.anno_id, hapmap.geno.snpval_id, row_number() OVER (?)
                InitPlan 2 (returns $1)
                  ->  Result  (cost=0.05..0.06 rows=1 width=0) (actual time=0.046..0.046 rows=1 loops=1)
                        Output: $0
                        InitPlan 1 (returns $0)
                          ->  Limit  (cost=0.00..0.05 rows=1 width=4) (actual time=0.042..0.042 rows=1 loops=1)
                                Output: hapmap.idlink.id
                                ->  Index Scan using idlink_pkey on idlink  (cost=0.00..49.89 rows=909 width=4) (actual time=0.042..0.042 rows=1 loops=1)
                                      Output: hapmap.idlink.id
                                      Filter: (id IS NOT NULL)
                ->  Sort  (cost=2401661.23..2403932.22 rows=908398 width=53) (actual time=374088.717..374387.921 rows=908398 loops=1)
                      Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred, hapmap.geno.idlink_id, hapmap.geno.anno_id, hapmap.geno.snpval_id
                      Sort Key: anno.rsid, anno.id
                      Sort Method:  quicksort  Memory: 152320kB
                      ->  Hash Join  (cost=46204.93..2311761.78 rows=908398 width=53) (actual time=1754.959..366112.108 rows=908398 loops=1)
                            Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred, hapmap.geno.idlink_id, hapmap.geno.anno_id, hapmap.geno.snpval_id
                            Hash Cond: (hapmap.geno.anno_id = anno.id)
                            ->  Bitmap Heap Scan on geno  (cost=17094.90..2263348.29 rows=908398 width=12) (actual time=1219.273..362864.507 rows=908398 loops=1)
                                  Output: hapmap.geno.idlink_id, hapmap.geno.anno_id, hapmap.geno.snpval_id
                                  Recheck Cond: (idlink_id = $1)
                                  ->  Bitmap Index Scan on geno_pkey  (cost=0.00..16867.80 rows=908398 width=0) (actual time=520.768..520.768 rows=908398 loops=1)
                                        Index Cond: (idlink_id = $1)
                            ->  Hash  (cost=17447.79..17447.79 rows=932979 width=41) (actual time=534.798..534.798 rows=932979 loops=1)
                                  Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred
                                  ->  Seq Scan on anno  (cost=0.00..17447.79 rows=932979 width=41) (actual time=0.020..210.699 rows=932979 loops=1)
                                        Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred
  ->  Sort  (cost=56855882.72..57144683.54 rows=115520330 width=42) (actual time=23027732.092..37113627.380 rows=823086774 loops=1)
        Output: (CASE WHEN (hapmap.geno.snpval_id = (-1)) THEN '0 0'::text WHEN (hapmap.geno.snpval_id = 0) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.allelea_id)::text) WHEN (hapmap.geno.snpval_id = 1) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) WHEN (hapmap.geno.snpval_id = 2) THEN (((dedup_patient_anno.alleleb_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) ELSE NULL::text END), hapmap.geno.idlink_id, hapmap.geno.anno_id, pheno.patientid, pheno.phenotype, sex.code
        Sort Key: hapmap.geno.idlink_id, sex.code, pheno.phenotype, pheno.patientid, hapmap.geno.anno_id
        Sort Method:  external merge  Disk: 30573712kB
        ->  Hash Join  (cost=27074475.72..37832041.61 rows=115520330 width=42) (actual time=2408623.440..3822532.653 rows=823086774 loops=1)
              Output: CASE WHEN (hapmap.geno.snpval_id = (-1)) THEN '0 0'::text WHEN (hapmap.geno.snpval_id = 0) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.allelea_id)::text) WHEN (hapmap.geno.snpval_id = 1) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) WHEN (hapmap.geno.snpval_id = 2) THEN (((dedup_patient_anno.alleleb_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) ELSE NULL::text END, hapmap.geno.idlink_id, hapmap.geno.anno_id, pheno.patientid, pheno.phenotype, sex.code
              Hash Cond: (hapmap.geno.idlink_id = hapmap.idlink.id)
              ->  Hash Join  (cost=27074341.04..33355494.14 rows=115520330 width=28) (actual time=2408621.167..3014241.921 rows=823086774 loops=1)
                    Output: hapmap.geno.snpval_id, hapmap.geno.idlink_id, hapmap.geno.anno_id, dedup_patient_anno.allelea_id, dedup_patient_anno.alleleb_id
                    Hash Cond: (dedup_patient_anno.id = hapmap.geno.anno_id)
                    ->  CTE Scan on dedup_patient_anno  (cost=0.00..90.84 rows=4542 width=20) (actual time=374088.765..376769.058 rows=905486 loops=1)
                          Output: dedup_patient_anno.allelea_id, dedup_patient_anno.alleleb_id, dedup_patient_anno.id
                    ->  Hash  (cost=12720764.24..12720764.24 rows=825733824 width=12) (actual time=2034506.199..2034506.199 rows=825733782 loops=1)
                          Output: hapmap.geno.snpval_id, hapmap.geno.idlink_id, hapmap.geno.anno_id
                          ->  Seq Scan on geno  (cost=0.00..12720764.24 rows=825733824 width=12) (actual time=0.034..1777002.865 rows=825733782 loops=1)
                                Output: hapmap.geno.snpval_id, hapmap.geno.idlink_id, hapmap.geno.anno_id
              ->  Hash  (cost=123.32..123.32 rows=909 width=18) (actual time=2.237..2.237 rows=909 loops=1)
                    Output: hapmap.idlink.id, pheno.patientid, pheno.phenotype, sex.code
                    ->  Hash Join  (cost=75.10..123.32 rows=909 width=18) (actual time=0.672..1.890 rows=909 loops=1)
                          Output: hapmap.idlink.id, pheno.patientid, pheno.phenotype, sex.code
                          Hash Cond: ((pheno.sex_id)::text = (sex.val)::text)
                          ->  Hash Join  (cost=25.27..59.86 rows=909 width=16) (actual time=0.645..1.437 rows=909 loops=1)
                                Output: hapmap.idlink.id, pheno.patientid, pheno.phenotype, pheno.sex_id
                                Hash Cond: ((hapmap.idlink.patientid)::text = (pheno.patientid)::text)
                                ->  Seq Scan on idlink  (cost=0.00..22.09 rows=909 width=12) (actual time=0.028..0.319 rows=909 loops=1)
                                      Output: hapmap.idlink.id, hapmap.idlink.expid, hapmap.idlink.patientid, hapmap.idlink.studyid, hapmap.idlink.sampleid
                                ->  Hash  (cost=14.01..14.01 rows=901 width=12) (actual time=0.598..0.598 rows=901 loops=1)
                                      Output: pheno.patientid, pheno.phenotype, pheno.sex_id
                                      ->  Seq Scan on pheno  (cost=0.00..14.01 rows=901 width=12) (actual time=0.024..0.271 rows=901 loops=1)
                                            Output: pheno.patientid, pheno.phenotype, pheno.sex_id
                          ->  Hash  (cost=27.70..27.70 rows=1770 width=12) (actual time=0.016..0.016 rows=2 loops=1)
                                Output: sex.code, sex.val
                                ->  Seq Scan on sex  (cost=0.00..27.70 rows=1770 width=12) (actual time=0.014..0.015 rows=2 loops=1)
                                      Output: sex.code, sex.val
Total runtime: 37772744.449 ms
\end{Verbatim}

\subsubsection{PED bigjoin query  memory graph}

Figure~\ref{meminfo_ped_bigjoin} shows memory usage of the PED bigjoin query.

\begin{figure}[tph]
  \begin{center}
    \scalebox{0.5}{\includegraphics{meminfo_ped_bigjoin.pdf}}
  \caption{Memory plot of PED bigjoin query}
  \label{meminfo_ped_bigjoin}
  \end{center}
\end{figure}

\subsection{truncated PED}\label{ped_trunc}
\subsubsection{truncated PED query}
\begin{Verbatim}
   SET search_path TO %s, public;
   WITH dedup_patient_anno AS
     ( SELECT *
     FROM
             (SELECT  *,
                      row_number() OVER(PARTITION BY anno.rsid ORDER BY anno.id)
             FROM     anno
                      INNER JOIN geno
                      ON       anno.id = geno.anno_id
             WHERE    idlink_id        =
                      (SELECT MIN(id)
                      FROM    idlink
                      )
             ) AS s
     WHERE   row_number = '1'
     )
     SELECT array_to_string(array_agg(gvals_ord.g), E'\t') AS str
     FROM
     (
     SELECT decode_genotype(geno.snpval_id, allelea_id, alleleb_id) AS g,
     geno.idlink_id, geno.anno_id
     FROM    geno
     INNER JOIN dedup_patient_anno
     ON      geno.anno_id = dedup_patient_anno.id
     INNER JOIN idlink
     ON      geno.idlink_id = idlink.id
     ORDER BY idlink_id, anno_id
             ) AS gvals_ord
     GROUP BY  gvals_ord. idlink_id
     ORDER BY gvals_ord.idlink_id;
\end{Verbatim}

\subsubsection{truncated PED query plan (output of EXPLAIN ANALYSE VERBOSE)}
\begin{Verbatim}[fontsize=\relsize{-1}]
GroupAggregate  (cost=58497274.71..60518883.49 rows=200 width=36) (actual time=8605764.638..10901810.367 rows=909 loops=1)
  Output: array_to_string(array_agg((CASE WHEN (hapmap.geno.snpval_id = (-1)) THEN '0 0'::text WHEN (hapmap.geno.snpval_id = 0) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.allelea_id)::text) WHEN (hapmap.geno.snpval_id = 1) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) WHEN (hapmap.geno.snpval_id = 2) THEN (((dedup_patient_anno.alleleb_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) ELSE NULL::text END)), '	'::text)
  CTE dedup_patient_anno
    ->  Subquery Scan s  (cost=2401661.29..2431184.23 rows=4542 width=195) (actual time=508896.802..510595.754 rows=905486 loops=1)
          Output: s.id, s.fid, s.rsid, s.chromosome, s.location, s.allelea_id, s.alleleb_id, s.preferred, s.idlink_id, s.anno_id, s.snpval_id, s.row_number
          Filter: (s.row_number = 1::bigint)
          ->  WindowAgg  (cost=2401661.29..2419829.25 rows=908398 width=53) (actual time=508896.792..510296.878 rows=908398 loops=1)
                Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred, hapmap.geno.idlink_id, hapmap.geno.anno_id, hapmap.geno.snpval_id, row_number() OVER (?)
                InitPlan 2 (returns $1)
                  ->  Result  (cost=0.05..0.06 rows=1 width=0) (actual time=26.583..26.583 rows=1 loops=1)
                        Output: $0
                        InitPlan 1 (returns $0)
                          ->  Limit  (cost=0.00..0.05 rows=1 width=4) (actual time=26.580..26.580 rows=1 loops=1)
                                Output: hapmap.idlink.id
                                ->  Index Scan using idlink_pkey on idlink  (cost=0.00..49.89 rows=909 width=4) (actual time=26.579..26.579 rows=1 loops=1)
                                      Output: hapmap.idlink.id
                                      Filter: (id IS NOT NULL)
                ->  Sort  (cost=2401661.23..2403932.22 rows=908398 width=53) (actual time=508896.751..509218.585 rows=908398 loops=1)
                      Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred, hapmap.geno.idlink_id, hapmap.geno.anno_id, hapmap.geno.snpval_id
                      Sort Key: anno.rsid, anno.id
                      Sort Method:  quicksort  Memory: 152320kB
                      ->  Hash Join  (cost=46204.93..2311761.78 rows=908398 width=53) (actual time=2644.951..500617.437 rows=908398 loops=1)
                            Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred, hapmap.geno.idlink_id, hapmap.geno.anno_id, hapmap.geno.snpval_id
                            Hash Cond: (hapmap.geno.anno_id = anno.id)
                            ->  Bitmap Heap Scan on geno  (cost=17094.90..2263348.29 rows=908398 width=12) (actual time=1703.316..496792.327 rows=908398 loops=1)
                                  Output: hapmap.geno.idlink_id, hapmap.geno.anno_id, hapmap.geno.snpval_id
                                  Recheck Cond: (idlink_id = $1)
                                  ->  Bitmap Index Scan on geno_pkey  (cost=0.00..16867.80 rows=908398 width=0) (actual time=936.872..936.872 rows=908398 loops=1)
                                        Index Cond: (idlink_id = $1)
                            ->  Hash  (cost=17447.79..17447.79 rows=932979 width=41) (actual time=940.575..940.575 rows=932979 loops=1)
                                  Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred
                                  ->  Seq Scan on anno  (cost=0.00..17447.79 rows=932979 width=41) (actual time=0.503..597.944 rows=932979 loops=1)
                                        Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred
  ->  Sort  (cost=56066090.48..56354891.31 rows=115520330 width=28) (actual time=8603885.482..10398097.481 rows=823086774 loops=1)
        Output: (CASE WHEN (hapmap.geno.snpval_id = (-1)) THEN '0 0'::text WHEN (hapmap.geno.snpval_id = 0) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.allelea_id)::text) WHEN (hapmap.geno.snpval_id = 1) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) WHEN (hapmap.geno.snpval_id = 2) THEN (((dedup_patient_anno.alleleb_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) ELSE NULL::text END), hapmap.geno.idlink_id, hapmap.geno.anno_id
        Sort Key: hapmap.geno.idlink_id, hapmap.geno.anno_id
        Sort Method:  external merge  Disk: 17700552kB
        ->  Hash Join  (cost=27074374.49..37831940.38 rows=115520330 width=28) (actual time=2715795.709..4002935.156 rows=823086774 loops=1)
              Output: CASE WHEN (hapmap.geno.snpval_id = (-1)) THEN '0 0'::text WHEN (hapmap.geno.snpval_id = 0) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.allelea_id)::text) WHEN (hapmap.geno.snpval_id = 1) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) WHEN (hapmap.geno.snpval_id = 2) THEN (((dedup_patient_anno.alleleb_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) ELSE NULL::text END, hapmap.geno.idlink_id, hapmap.geno.anno_id
              Hash Cond: (hapmap.geno.idlink_id = hapmap.idlink.id)
              ->  Hash Join  (cost=27074341.04..33355494.14 rows=115520330 width=28) (actual time=2715774.933..3270158.476 rows=823086774 loops=1)
                    Output: hapmap.geno.snpval_id, hapmap.geno.idlink_id, hapmap.geno.anno_id, dedup_patient_anno.allelea_id, dedup_patient_anno.alleleb_id
                    Hash Cond: (dedup_patient_anno.id = hapmap.geno.anno_id)
                    ->  CTE Scan on dedup_patient_anno  (cost=0.00..90.84 rows=4542 width=20) (actual time=508896.808..511599.567 rows=905486 loops=1)
                          Output: dedup_patient_anno.allelea_id, dedup_patient_anno.alleleb_id, dedup_patient_anno.id
                    ->  Hash  (cost=12720764.24..12720764.24 rows=825733824 width=12) (actual time=2206845.428..2206845.428 rows=825733782 loops=1)
                          Output: hapmap.geno.snpval_id, hapmap.geno.idlink_id, hapmap.geno.anno_id
                          ->  Seq Scan on geno  (cost=0.00..12720764.24 rows=825733824 width=12) (actual time=0.034..1948753.704 rows=825733782 loops=1)
                                Output: hapmap.geno.snpval_id, hapmap.geno.idlink_id, hapmap.geno.anno_id
              ->  Hash  (cost=22.09..22.09 rows=909 width=4) (actual time=20.741..20.741 rows=909 loops=1)
                    Output: hapmap.idlink.id
                    ->  Seq Scan on idlink  (cost=0.00..22.09 rows=909 width=4) (actual time=0.019..20.490 rows=909 loops=1)
                          Output: hapmap.idlink.id
Total runtime: 10905962.248 ms
\end{Verbatim}

\subsubsection{truncated PED query memory graph}

Figure~\ref{meminfo_ped_trunc} shows memory usage of the truncated PED query.

\begin{figure}[tph]
  \begin{center}
    \scalebox{0.5}{\includegraphics{meminfo_ped_trunc.pdf}}
    %\resizebox{14cm}{6cm}{\includegraphics{schema.eps}}
  \caption{Memory plot of truncated PED query}
  \label{meminfo_ped_trunc}
  \end{center}
\end{figure}

\subsection{PED phenoout}\label{ped_phenoout}
\subsubsection{PED phenoout query}
\begin{Verbatim}
   WITH dedup_patient_anno AS
     ( SELECT *
     FROM
             (SELECT  *,
                      row_number() OVER(PARTITION BY anno.rsid ORDER BY anno.id)
             FROM     anno
                      INNER JOIN geno
                      ON       anno.id = geno.anno_id
             WHERE    idlink_id        =
                      (SELECT MIN(id)
                      FROM    idlink
                      )
             ) AS s
     WHERE   row_number = '1'
     )
     SELECT          pheno.patientid
                  || E'\t'
                  || pheno.patientid
                  || E'\t'
                  || 0
                  || E'\t'
                  || 0
                  || E'\t'
                  || COALESCE(sex.code::text, '')
                  || E'\t'
                  || COALESCE(pheno.phenotype::text,'')
                  || E'\t'
                  || genostr.str
     FROM
     (
     SELECT gvals_ord.idlink_id, gvals_ord.patientid, array_to_string(array_agg(gvals_ord.g), E'\t') AS str
     FROM
     (
     SELECT idlink.patientid, geno.idlink_id, decode_genotype(geno.snpval_id, allelea_id, alleleb_id) AS g
     FROM      geno
     INNER JOIN dedup_patient_anno
     ON        geno.anno_id = dedup_patient_anno.id
     INNER JOIN idlink
     ON        geno.idlink_id = idlink.id
     ORDER BY geno.idlink_id, geno.anno_id
             ) AS gvals_ord
     GROUP BY gvals_ord.idlink_id, gvals_ord.patientid
     ) AS genostr
     INNER JOIN pheno
     ON        genostr.patientid = pheno.patientid
     INNER JOIN sex
     ON        pheno.sex_id=sex.val
     ORDER BY  genostr.idlink_id;
\end{Verbatim}

\subsubsection{PED phenoout query plan (output of EXPLAIN ANALYSE VERBOSE)}
\begin{Verbatim}[fontsize=\relsize{-1}]
Sort  (cost=61208822.97..61208922.97 rows=40000 width=50) (actual time=12131349.711..12134027.990 rows=909 loops=1)
  Output: ((((((((((((((pheno.patientid)::text || '	'::text) || (pheno.patientid)::text) || '	'::text) || '0'::text) || '	'::text) || '0'::text) || '	'::text) || COALESCE((sex.code)::text, ''::text)) || '	'::text) || COALESCE((pheno.phenotype)::text, ''::text)) || '	'::text) || (array_to_string(array_agg((CASE WHEN (hapmap.geno.snpval_id = (-1)) THEN '0 0'::text WHEN (hapmap.geno.snpval_id = 0) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.allelea_id)::text) WHEN (hapmap.geno.snpval_id = 1) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) WHEN (hapmap.geno.snpval_id = 2) THEN (((dedup_patient_anno.alleleb_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) ELSE NULL::text END)), '	'::text))))
  Sort Key: hapmap.geno.idlink_id
  Sort Method:  external merge  Disk: 3218352kB
  CTE dedup_patient_anno
    ->  Subquery Scan s  (cost=2401661.29..2431184.23 rows=4542 width=195) (actual time=871919.518..873604.571 rows=905486 loops=1)
          Output: s.id, s.fid, s.rsid, s.chromosome, s.location, s.allelea_id, s.alleleb_id, s.preferred, s.idlink_id, s.anno_id, s.snpval_id, s.row_number
          Filter: (s.row_number = 1::bigint)
          ->  WindowAgg  (cost=2401661.29..2419829.25 rows=908398 width=53) (actual time=871919.507..873308.545 rows=908398 loops=1)
                Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred, hapmap.geno.idlink_id, hapmap.geno.anno_id, hapmap.geno.snpval_id, row_number() OVER (?)
                InitPlan 2 (returns $1)
                  ->  Result  (cost=0.05..0.06 rows=1 width=0) (actual time=0.026..0.026 rows=1 loops=1)
                        Output: $0
                        InitPlan 1 (returns $0)
                          ->  Limit  (cost=0.00..0.05 rows=1 width=4) (actual time=0.020..0.020 rows=1 loops=1)
                                Output: hapmap.idlink.id
                                ->  Index Scan using idlink_pkey on idlink  (cost=0.00..49.89 rows=909 width=4) (actual time=0.019..0.019 rows=1 loops=1)
                                      Output: hapmap.idlink.id
                                      Filter: (id IS NOT NULL)
                ->  Sort  (cost=2401661.23..2403932.22 rows=908398 width=53) (actual time=871912.498..872221.407 rows=908398 loops=1)
                      Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred, hapmap.geno.idlink_id, hapmap.geno.anno_id, hapmap.geno.snpval_id
                      Sort Key: anno.rsid, anno.id
                      Sort Method:  quicksort  Memory: 152320kB
                      ->  Hash Join  (cost=46204.93..2311761.78 rows=908398 width=53) (actual time=1688.624..863579.930 rows=908398 loops=1)
                            Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred, hapmap.geno.idlink_id, hapmap.geno.anno_id, hapmap.geno.snpval_id
                            Hash Cond: (hapmap.geno.anno_id = anno.id)
                            ->  Bitmap Heap Scan on geno  (cost=17094.90..2263348.29 rows=908398 width=12) (actual time=1197.400..859965.222 rows=908398 loops=1)
                                  Output: hapmap.geno.idlink_id, hapmap.geno.anno_id, hapmap.geno.snpval_id
                                  Recheck Cond: (idlink_id = $1)
                                  ->  Bitmap Index Scan on geno_pkey  (cost=0.00..16867.80 rows=908398 width=0) (actual time=498.745..498.745 rows=908398 loops=1)
                                        Index Cond: (idlink_id = $1)
                            ->  Hash  (cost=17447.79..17447.79 rows=932979 width=41) (actual time=490.324..490.324 rows=932979 loops=1)
                                  Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred
                                  ->  Seq Scan on anno  (cost=0.00..17447.79 rows=932979 width=41) (actual time=0.014..176.059 rows=932979 loops=1)
                                        Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred
  ->  Hash Join  (cost=58771431.20..58774581.20 rows=40000 width=50) (actual time=11919479.786..12119733.581 rows=909 loops=1)
        Output: (((((((((((((pheno.patientid)::text || '	'::text) || (pheno.patientid)::text) || '	'::text) || '0'::text) || '	'::text) || '0'::text) || '	'::text) || COALESCE((sex.code)::text, ''::text)) || '	'::text) || COALESCE((pheno.phenotype)::text, ''::text)) || '	'::text) || (array_to_string(array_agg((CASE WHEN (hapmap.geno.snpval_id = (-1)) THEN '0 0'::text WHEN (hapmap.geno.snpval_id = 0) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.allelea_id)::text) WHEN (hapmap.geno.snpval_id = 1) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) WHEN (hapmap.geno.snpval_id = 2) THEN (((dedup_patient_anno.alleleb_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) ELSE NULL::text END)), '	'::text)))
        Hash Cond: ((hapmap.idlink.patientid)::text = (pheno.patientid)::text)
        ->  HashAggregate  (cost=58771342.58..58771942.58 rows=40000 width=94) (actual time=11919412.640..12118317.056 rows=909 loops=1)
              Output: hapmap.geno.idlink_id, hapmap.idlink.patientid, array_to_string(array_agg((CASE WHEN (hapmap.geno.snpval_id = (-1)) THEN '0 0'::text WHEN (hapmap.geno.snpval_id = 0) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.allelea_id)::text) WHEN (hapmap.geno.snpval_id = 1) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) WHEN (hapmap.geno.snpval_id = 2) THEN (((dedup_patient_anno.alleleb_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) ELSE NULL::text END)), '	'::text)
              ->  Sort  (cost=56460935.98..56749736.81 rows=115520330 width=36) (actual time=9320590.368..11438428.245 rows=823086774 loops=1)
                    Output: hapmap.idlink.patientid, hapmap.geno.idlink_id, (CASE WHEN (hapmap.geno.snpval_id = (-1)) THEN '0 0'::text WHEN (hapmap.geno.snpval_id = 0) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.allelea_id)::text) WHEN (hapmap.geno.snpval_id = 1) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) WHEN (hapmap.geno.snpval_id = 2) THEN (((dedup_patient_anno.alleleb_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) ELSE NULL::text END)
                    Sort Key: hapmap.geno.idlink_id, hapmap.geno.anno_id
                    Sort Method:  external merge  Disk: 24137056kB
                    ->  Hash Join  (cost=27074374.49..37831940.38 rows=115520330 width=36) (actual time=3299803.119..4602225.962 rows=823086774 loops=1)
                          Output: hapmap.idlink.patientid, hapmap.geno.idlink_id, CASE WHEN (hapmap.geno.snpval_id = (-1)) THEN '0 0'::text WHEN (hapmap.geno.snpval_id = 0) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.allelea_id)::text) WHEN (hapmap.geno.snpval_id = 1) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) WHEN (hapmap.geno.snpval_id = 2) THEN (((dedup_patient_anno.alleleb_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) ELSE NULL::text END
                          Hash Cond: (hapmap.geno.idlink_id = hapmap.idlink.id)
                          ->  Hash Join  (cost=27074341.04..33355494.14 rows=115520330 width=28) (actual time=3299802.508..3836037.801 rows=823086774 loops=1)
                                Output: hapmap.geno.idlink_id, hapmap.geno.snpval_id, hapmap.geno.anno_id, dedup_patient_anno.allelea_id, dedup_patient_anno.alleleb_id
                                Hash Cond: (dedup_patient_anno.id = hapmap.geno.anno_id)
                                ->  CTE Scan on dedup_patient_anno  (cost=0.00..90.84 rows=4542 width=20) (actual time=871919.525..874604.105 rows=905486 loops=1)
                                      Output: dedup_patient_anno.allelea_id, dedup_patient_anno.alleleb_id, dedup_patient_anno.id
                                ->  Hash  (cost=12720764.24..12720764.24 rows=825733824 width=12) (actual time=2427857.105..2427857.105 rows=825733782 loops=1)
                                      Output: hapmap.geno.idlink_id, hapmap.geno.snpval_id, hapmap.geno.anno_id
                                      ->  Seq Scan on geno  (cost=0.00..12720764.24 rows=825733824 width=12) (actual time=0.034..2172360.008 rows=825733782 loops=1)
                                            Output: hapmap.geno.idlink_id, hapmap.geno.snpval_id, hapmap.geno.anno_id
                          ->  Hash  (cost=22.09..22.09 rows=909 width=12) (actual time=0.588..0.588 rows=909 loops=1)
                                Output: hapmap.idlink.patientid, hapmap.idlink.id
                                ->  Seq Scan on idlink  (cost=0.00..22.09 rows=909 width=12) (actual time=0.017..0.317 rows=909 loops=1)
                                      Output: hapmap.idlink.patientid, hapmap.idlink.id
        ->  Hash  (cost=77.35..77.35 rows=901 width=14) (actual time=0.985..0.985 rows=901 loops=1)
              Output: pheno.patientid, pheno.phenotype, sex.code
              ->  Hash Join  (cost=49.83..77.35 rows=901 width=14) (actual time=0.040..0.651 rows=901 loops=1)
                    Output: pheno.patientid, pheno.phenotype, sex.code
                    Hash Cond: ((pheno.sex_id)::text = (sex.val)::text)
                    ->  Seq Scan on pheno  (cost=0.00..14.01 rows=901 width=12) (actual time=0.013..0.171 rows=901 loops=1)
                          Output: pheno.patientid, pheno.sex_id, pheno.race_id, pheno.phenotype
                    ->  Hash  (cost=27.70..27.70 rows=1770 width=12) (actual time=0.009..0.009 rows=2 loops=1)
                          Output: sex.code, sex.val
                          ->  Seq Scan on sex  (cost=0.00..27.70 rows=1770 width=12) (actual time=0.004..0.004 rows=2 loops=1)
                                Output: sex.code, sex.val
Total runtime: 12174706.104 ms

\end{Verbatim}

\subsubsection{PED phenoout query memory graph}

Figure~\ref{meminfo_ped_phenoout} shows memory usage of the PED phenoout query.

\begin{figure}[tph]
  \begin{center}
    \scalebox{0.5}{\includegraphics{meminfo_ped_phenoout.pdf}}
  \caption{Memory plot of PED phenoout query}
  \label{meminfo_ped_phenoout}
  \end{center}
\end{figure}

\subsection{Hybrid PED}\label{ped_hybrid}
\subsubsection{Hybrid PED query}
\begin{Verbatim}
   gq = text("""
   SET search_path TO %s, public;
   WITH dedup_patient_anno AS
     ( SELECT *
     FROM
             (SELECT  *,
                      row_number() OVER(PARTITION BY anno.rsid ORDER BY anno.id)
             FROM     anno
                      INNER JOIN geno
                      ON       anno.id = geno.anno_id
             WHERE    idlink_id        =
                      (SELECT MIN(id)
                      FROM    idlink
                      )
             ) AS s
     WHERE   row_number = '1'
     )
     SELECT array_to_string(array_agg(gvals_ord.g), E'\t') AS str
     FROM
     (
     SELECT decode_genotype(geno.snpval_id, allelea_id, alleleb_id) AS g,
     geno.idlink_id, geno.anno_id
     FROM    geno
     INNER JOIN dedup_patient_anno
     ON      geno.anno_id = dedup_patient_anno.id
     INNER JOIN idlink
     ON      geno.idlink_id = idlink.id
     ORDER BY idlink_id, anno_id
             ) AS gvals_ord
     GROUP BY  gvals_ord. idlink_id
     ORDER BY gvals_ord.idlink_id;
   """%(schemaname))
   genostr = conn.execute(gq).fetchall()
   pq = text("""
   SET search_path TO %s, public;
   SELECT
                  pheno.patientid
                  || E'\t'
                  || pheno.patientid
                  || E'\t'
                  || 0
                  || E'\t'
                  || 0
                  || E'\t'
                  || COALESCE(sex.code::text, '')
                  || E'\t'
                  || COALESCE(pheno.phenotype::text,'')
                  || E'\t'
     FROM idlink
     INNER JOIN pheno
     ON      idlink.patientid = pheno.patientid
     INNER JOIN sex
     ON      pheno.sex_id=sex.val
     ORDER BY idlink.id;
    """%(schemaname))
   phenostr = conn.execute(pq).fetchall()
   f = open(filename, 'w')
   for grow, prow in zip(genostr, phenostr):
      grow = [str(x) for x in grow]
      prow = [str(x) for x in prow]
      f.write("\t".join(prow))
      f.write("\t".join(grow)+'\n')
   f.close()
   conn.close()
\end{Verbatim}

\section{TPED}\label{tped}
\subsection{TPED bigjoin}\label{tped_bigjoin}
\subsubsection{TPED bigjoin query}
\begin{Verbatim}[fontsize=\relsize{-1}]
   WITH dedup_patient_anno AS
     ( SELECT *
     FROM
             (SELECT  *,
                      row_number() OVER(PARTITION BY anno.rsid ORDER BY anno.id)
             FROM     anno
                      INNER JOIN geno
                      ON       anno.id = geno.anno_id
             WHERE    idlink_id        =
                      (SELECT MIN(id)
                      FROM    idlink
                      )
             ) AS s
     WHERE   row_number = '1'
     )
   SELECT               name
                     || E'\t'
                     || rsid
                     || E'\t'
                     || 0
                     || E'\t'
                     || location
                     || E'\t'
                     || array_to_string(array_agg(gvals_ord.g), E'\t') AS str
     FROM
     (
     SELECT decode_genotype(geno.snpval_id, allelea_id, alleleb_id) AS g,
     chromo.name, dedup_patient_anno.rsid, dedup_patient_anno.location, geno.anno_id, geno.idlink_id
     FROM    geno
     INNER JOIN dedup_patient_anno
     ON      geno.anno_id = dedup_patient_anno.id
     INNER JOIN chromo
     ON      dedup_patient_anno.chromosome = chromo.id
     ORDER BY anno_id, name, rsid, location, idlink_id
             ) AS gvals_ord
     GROUP BY gvals_ord.anno_id, gvals_ord.name, gvals_ord.rsid, gvals_ord.location
     ORDER BY gvals_ord.anno_id;
\end{Verbatim}

\subsubsection{TPED bigjoin query plan  (output of EXPLAIN ANALYZE VERBOSE)}
\begin{Verbatim}[fontsize=\relsize{-1}]
GroupAggregate  (cost=60462640.68..63812730.25 rows=11552033 width=110) (actual time=18379183.825..19564198.039 rows=905486 loops=1)
  Output: (((((((((chromo.name)::text || '	'::text) || (dedup_patient_anno.rsid)::text) || '	'::text) || '0'::text) || '	'::text) || (dedup_patient_anno.location)::text) || '	'::text) || array_to_string(array_agg((CASE WHEN (hapmap.geno.snpval_id = (-1)) THEN '0 0'::text WHEN (hapmap.geno.snpval_id = 0) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.allelea_id)::text) WHEN (hapmap.geno.snpval_id = 1) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) WHEN (hapmap.geno.snpval_id = 2) THEN (((dedup_patient_anno.alleleb_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) ELSE NULL::text END)), '	'::text))
  CTE dedup_patient_anno
    ->  Subquery Scan s  (cost=2401661.29..2431184.23 rows=4542 width=195) (actual time=1021994.102..1023764.331 rows=905486 loops=1)
          Output: s.id, s.fid, s.rsid, s.chromosome, s.location, s.allelea_id, s.alleleb_id, s.preferred, s.idlink_id, s.anno_id, s.snpval_id, s.row_number
          Filter: (s.row_number = 1::bigint)
          ->  WindowAgg  (cost=2401661.29..2419829.25 rows=908398 width=53) (actual time=1021994.093..1023470.721 rows=908398 loops=1)
                Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred, hapmap.geno.idlink_id, hapmap.geno.anno_id, hapmap.geno.snpval_id, row_number() OVER (?)
                InitPlan 2 (returns $1)
                  ->  Result  (cost=0.05..0.06 rows=1 width=0) (actual time=32.965..32.965 rows=1 loops=1)
                        Output: $0
                        InitPlan 1 (returns $0)
                          ->  Limit  (cost=0.00..0.05 rows=1 width=4) (actual time=32.961..32.961 rows=1 loops=1)
                                Output: hapmap.idlink.id
                                ->  Index Scan using idlink_pkey on idlink  (cost=0.00..49.89 rows=909 width=4) (actual time=32.960..32.960 rows=1 loops=1)
                                      Output: hapmap.idlink.id
                                      Filter: (id IS NOT NULL)
                ->  Sort  (cost=2401661.23..2403932.22 rows=908398 width=53) (actual time=1021994.063..1022344.959 rows=908398 loops=1)
                      Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred, hapmap.geno.idlink_id, hapmap.geno.anno_id, hapmap.geno.snpval_id
                      Sort Key: anno.rsid, anno.id
                      Sort Method:  quicksort  Memory: 152320kB
                      ->  Hash Join  (cost=46204.93..2311761.78 rows=908398 width=53) (actual time=2880.537..1013436.167 rows=908398 loops=1)
                            Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred, hapmap.geno.idlink_id, hapmap.geno.anno_id, hapmap.geno.snpval_id
                            Hash Cond: (hapmap.geno.anno_id = anno.id)
                            ->  Bitmap Heap Scan on geno  (cost=17094.90..2263348.29 rows=908398 width=12) (actual time=1752.097..1009281.457 rows=908398 loops=1)
                                  Output: hapmap.geno.idlink_id, hapmap.geno.anno_id, hapmap.geno.snpval_id
                                  Recheck Cond: (idlink_id = $1)
                                  ->  Bitmap Index Scan on geno_pkey  (cost=0.00..16867.80 rows=908398 width=0) (actual time=953.308..953.308 rows=908398 loops=1)
                                        Index Cond: (idlink_id = $1)
                            ->  Hash  (cost=17447.79..17447.79 rows=932979 width=41) (actual time=1127.344..1127.344 rows=932979 loops=1)
                                  Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred
                                  ->  Seq Scan on anno  (cost=0.00..17447.79 rows=932979 width=41) (actual time=12.066..788.299 rows=932979 loops=1)
                                        Output: anno.id, anno.fid, anno.rsid, anno.chromosome, anno.location, anno.allelea_id, anno.alleleb_id, anno.preferred
  ->  Sort  (cost=58031456.45..58320257.27 rows=115520330 width=102) (actual time=18379074.271..19021086.033 rows=823086774 loops=1)
        Output: (CASE WHEN (hapmap.geno.snpval_id = (-1)) THEN '0 0'::text WHEN (hapmap.geno.snpval_id = 0) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.allelea_id)::text) WHEN (hapmap.geno.snpval_id = 1) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) WHEN (hapmap.geno.snpval_id = 2) THEN (((dedup_patient_anno.alleleb_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) ELSE NULL::text END), chromo.name, dedup_patient_anno.rsid, dedup_patient_anno.location, hapmap.geno.anno_id, hapmap.geno.idlink_id
        Sort Key: hapmap.geno.anno_id, chromo.name, dedup_patient_anno.rsid, dedup_patient_anno.location, hapmap.geno.idlink_id
        Sort Method:  external merge  Disk: 32390888kB
        ->  Hash Join  (cost=27074390.87..36243700.34 rows=115520330 width=102) (actual time=3582112.406..4489361.585 rows=823086774 loops=1)
              Output: CASE WHEN (hapmap.geno.snpval_id = (-1)) THEN '0 0'::text WHEN (hapmap.geno.snpval_id = 0) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.allelea_id)::text) WHEN (hapmap.geno.snpval_id = 1) THEN (((dedup_patient_anno.allelea_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) WHEN (hapmap.geno.snpval_id = 2) THEN (((dedup_patient_anno.alleleb_id)::text || ' '::text) || (dedup_patient_anno.alleleb_id)::text) ELSE NULL::text END, chromo.name, dedup_patient_anno.rsid, dedup_patient_anno.location, hapmap.geno.anno_id, hapmap.geno.idlink_id
              Hash Cond: (dedup_patient_anno.id = hapmap.geno.anno_id)
              ->  Hash Join  (cost=49.83..208.80 rows=4542 width=94) (actual time=1022009.266..1025192.147 rows=905486 loops=1)
                    Output: dedup_patient_anno.allelea_id, dedup_patient_anno.alleleb_id, dedup_patient_anno.rsid, dedup_patient_anno.location, dedup_patient_anno.id, chromo.name
                    Hash Cond: (dedup_patient_anno.chromosome = chromo.id)
                    ->  CTE Scan on dedup_patient_anno  (cost=0.00..90.84 rows=4542 width=86) (actual time=1021994.109..1024631.274 rows=905486 loops=1)
                          Output: dedup_patient_anno.id, dedup_patient_anno.fid, dedup_patient_anno.rsid, dedup_patient_anno.chromosome, dedup_patient_anno.location, dedup_patient_anno.allelea_id, dedup_patient_anno.alleleb_id, dedup_patient_anno.preferred, dedup_patient_anno.idlink_id, dedup_patient_anno.anno_id, dedup_patient_anno.snpval_id, dedup_patient_anno.row_number
                    ->  Hash  (cost=27.70..27.70 rows=1770 width=16) (actual time=15.127..15.127 rows=26 loops=1)
                          Output: chromo.name, chromo.id
                          ->  Seq Scan on chromo  (cost=0.00..27.70 rows=1770 width=16) (actual time=15.109..15.114 rows=26 loops=1)
                                Output: chromo.name, chromo.id
              ->  Hash  (cost=12720764.24..12720764.24 rows=825733824 width=12) (actual time=2560070.340..2560070.340 rows=825733782 loops=1)
                    Output: hapmap.geno.snpval_id, hapmap.geno.anno_id, hapmap.geno.idlink_id
                    ->  Seq Scan on geno  (cost=0.00..12720764.24 rows=825733824 width=12) (actual time=0.020..2302497.450 rows=825733782 loops=1)
                          Output: hapmap.geno.snpval_id, hapmap.geno.anno_id, hapmap.geno.idlink_id
Total runtime: 19571449.656 ms
\end{Verbatim}

\subsubsection{TPED bigjoin query memory graph}

Figure~\ref{meminfo_tped_bigjoin} shows memory usage of the TPED bigjoin query.

\begin{figure}[tph]
  \begin{center}
    \scalebox{0.5}{\includegraphics{meminfo_tped_bigjoin.pdf}}
  \caption{Memory plot of TPED bigjoin query}
  \label{meminfo_tped_bigjoin}
\end{center}
\end{figure}

\subsection{Hybrid TPED}\label{tped_hybrid}
\subsubsection{Hybrid TPED query}
\begin{Verbatim}[fontsize=\relsize{-1}]
   gq = text("""
   SET search_path TO %s, public;
   WITH dedup_patient_anno AS
     ( SELECT *
     FROM
             (SELECT  *,
                      row_number() OVER(PARTITION BY anno.rsid ORDER BY anno.id)
             FROM     anno
                      INNER JOIN geno
                      ON       anno.id = geno.anno_id
             WHERE    idlink_id        =
                      (SELECT MIN(id)
                      FROM    idlink
                      )
             ) AS s
     WHERE   row_number = '1'
     )
   SELECT array_to_string(array_agg(gvals_ord.g), E'\t') AS str
     FROM
     (
     SELECT geno.anno_id, decode_genotype(geno.snpval_id, allelea_id, alleleb_id) AS g
     FROM    geno
     INNER JOIN dedup_patient_anno
     ON      geno.anno_id = dedup_patient_anno.id
     ORDER BY geno.anno_id, geno.idlink_id
             ) AS gvals_ord
     GROUP BY gvals_ord.anno_id
     ORDER BY gvals_ord.anno_id;
   """%(schemaname))
   pq = text("""
   SET search_path TO %s, public;
   WITH dedup_patient_anno AS
     ( SELECT *
     FROM
             (SELECT  *,
                      row_number() OVER(PARTITION BY anno.rsid ORDER BY anno.id)
             FROM     anno
                      INNER JOIN geno
                      ON       anno.id = geno.anno_id
             WHERE    idlink_id        =
                      (SELECT MIN(id)
                      FROM    idlink
                      )
             ) AS s
     WHERE   row_number = '1'
     )
   SELECT               name
                     || E'\t'
                     || rsid
                     || E'\t'
                     || 0
                     || E'\t'
                     || location
                     || E'\t'
   FROM    dedup_patient_anno
   INNER JOIN chromo
   ON      dedup_patient_anno.chromosome = chromo.id
   ORDER BY anno_id
    """%(schemaname))
   genostr = conn.execute(gq).fetchall()
   phenostr = conn.execute(pq).fetchall()
   f = open(filename, 'w')
   for grow, prow in zip(genostr, phenostr):
      grow = [str(x) for x in grow]
      prow = [str(x) for x in prow]
      #f.write("\t".join(prow)+ "\t" + "\t".join(prow)+'\n')
      f.write("\t".join(prow))
      f.write("\t".join(grow)+'\n')
   f.close()
\end{Verbatim}
\end{document}
